USE SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetUserDetails')
	BEGIN
		DROP  Procedure  dbo.GetUserDetails
	END
GO

CREATE Procedure [dbo].[GetUserDetails]
	@PKID		nvarchar(100) = NULL
AS
BEGIN

	declare 
		@sql			varchar(max)
	,	@divisions		varchar(max)
	,	@managers		varchar(max)


	SELECT	@sql = ''
		,	@divisions = dbo.GetDivisionsXml()
		,	@managers = '<MANAGERS><entry id="0"></entry></MANAGERS>'




	;WITH PUsr AS (
		SELECT 
			p.PartnerNo			AS UserNo
		,	p.PartnerLastName	AS LastName
		,	p.PartnerFirstName	AS FirstName
		,	p.PartnerGUID		AS UserGUID	
		,	p.IsActive			AS IsActive
		,	p.PartnerDivision	AS DivisionID
		FROM TR_PARTNER as p WITH (NOLOCK)
		WHERE
			CAST(p.PartnerGUID AS VARCHAR(40)) = ISNULL(@PKID, '')
		UNION
		SELECT TOP 1
			'0'			AS UserNo
		,	''			AS LastName
		,	''			AS FirstName
		,	NewID()		AS UserGUID	
		,	'1'			AS IsActive
		,	'1'			AS DivisionID
		FROM TR_PARTNER as p WITH (NOLOCK)
		WHERE
			ISNULL(@PKID, '') = ''
	), UsrDtls AS (
		SELECT
			u.LoginID
		,	u.Password
		,	u.CanCreateParentAcct
		,	u.CanEditProductMaster
		,	u.ManagerId
		,	u.isSuperUser
		,	p.UserGUID
		FROM TR_USER as u
		INNER JOIN PUsr as p
			on p.UserGUID = u.PartnerGUID
	), FinalXML AS (
		SELECT 1 AS ID, N'<DATA>' AS Details 
		UNION
		SELECT 2 AS ID, N'<RESPONSE><RESULT>S</RESULT><MESSAGE></MESSAGE></RESPONSE>'
		UNION
		SELECT 2.1 as ID, @divisions as Details
		UNION
		SELECT 2.2 as ID, @managers as Details
		UNION
		SELECT
			3 AS ID, 
		+	'<PKID><![CDATA[' + CAST(p.UserGUID AS VARCHAR(40)) + ']]></PKID>'
		+	'<USERGUID><![CDATA[' + CAST(p.UserGUID AS VARCHAR(40)) + ']]></USERGUID>'
		+	'<USERNO><![CDATA[' + ISNULL(CAST(p.USERNO AS VARCHAR), '0') + ']]></USERNO>'
		+	'<LASTNAME><![CDATA[' + ISNULL(p.LASTNAME,'') + ']]></LASTNAME>'
		+	'<FIRSTNAME><![CDATA[' + ISNULL(p.FIRSTNAME,'') + ']]></FIRSTNAME>'
		+	'<LOGINID><![CDATA[' + ISNULL(LOGINID,'') + ']]></LOGINID>'
		+	'<PASSWORD><![CDATA[' + ISNULL([PASSWORD],'') + ']]></PASSWORD>'
		+	'<CANCREATEPARENTACCT><![CDATA[' + (CASE WHEN ISNULL(CANCREATEPARENTACCT ,0) = 1 THEN 'X' ELSE '' END) + ']]></CANCREATEPARENTACCT>'
		+	'<CANEDITPRODUCTMASTER><![CDATA[' + (CASE WHEN ISNULL(CANEDITPRODUCTMASTER ,0) = 1 THEN 'X' ELSE '' END) + ']]></CANEDITPRODUCTMASTER>'
		+	'<ISSUPERUSER><![CDATA[' + (CASE WHEN ISNULL(ISSUPERUSER,0) = 1 THEN 'X' ELSE '' END) + ']]></ISSUPERUSER>'
		+	'<ISACTIVE><![CDATA[' + (CASE WHEN ISNULL(IsActive,1) = 1 THEN 'X' ELSE '' END) + ']]></ISACTIVE>' 
		+	'<PARTNERDIVISIONID><![CDATA[' + ISNULL(CAST(p.DIVISIONID AS VARCHAR),'0')+ ']]></PARTNERDIVISIONID>' 
		+	'<MANAGERID><![CDATA[' + ISNULL(CAST(u.MANAGERID AS VARCHAR),'0')+ ']]></MANAGERID>' 
					AS Details
		FROM PUsr as p
		LEFT OUTER JOIN UsrDtls as u
			ON p.UserGUID = u.UserGUID
		UNION
		SELECT 4 AS ID, '</DATA>' AS Details 
	)

	SELECT @sql = @sql + Details
	FROM FinalXML
	Order by ID

	select @sql	AS Detail
END



